6.2 多关系数据查询 — 知识点讲解

多关系查询的两种写法

📖 核心概念

当需要查询的数据分布在多张表中时，需要通过连接（JOIN）把这些表关联起来。SQL 提供了两种语法来实现。

💬 人话翻译

就像查字典时你用"拼音"把一个字的读音和释义"连"起来一样——数据库用共同的列（比如学号）把不同表的行"拼"在一起。

写法一：WHERE 子句连接（传统写法）

在 FROM 后面列出所有要用到的表，再在 WHERE 中写出“哪两列相等”这个连接条件。

SQL 语法

SELECT 字段名
FROM 表1, 表2
WHERE 表1.共同列 = 表2.共同列;

💬 搭配例子看

例如：学生姓名在 student 表里，班级名称在 class 表里，它们都包含 class_id，所以可以按 class_id 把两张表连起来。

先看原始数据

下面这两个表，才是数据库里真正存着的数据。连接查询不是“凭空查出来”的，而是把这两张表按共同列对上号。

student 表

id	name	class_id
1	张三	101
2	李四	102
3	王五	101

class 表

class_id	class_name
101	软件1班
102	大数据1班

怎么配对

看 class_id：101 对 101，102 对 102。所以张三和王五会连到“软件1班”，李四会连到“大数据1班”。

SQL 例子

SELECT student.name, class.class_name
FROM student, class
WHERE student.class_id = class.class_id;

这句 SQL 在干什么

SELECT student.name, class.class_name
从结果里拿出两个字段：学生姓名、班级名称。

FROM student, class
让 student 表和 class 表一起参与查询。

WHERE student.class_id = class.class_id
按 class_id 相等来配对。也就是：学生表里的班级编号，要等于班级表里的班级编号。

这句 SQL 怎么读

可以顺着读成：

从 student 表和 class 表中，查询 student 表里的 name 字段和 class 表里的 class_name 字段，条件是 student.class_id 等于 class.class_id。

其中 WHERE 这句最关键，可以读成：“条件是学生表的 class_id 要等于班级表的 class_id。”

查询结果示意

name	class_name
张三	软件1班
李四	大数据1班
王五	软件1班

一句话理解

这种写法的意思就是：先把两张表放进来，再在 WHERE 里说明“按哪一列配对”。

写法二：JOIN 关键字连接（推荐写法）

用 JOIN 明确写出“和哪张表连接”，再用 ON 指定“按什么条件连接”。

SQL 语法

SELECT 字段名
FROM 表1
  INNER JOIN 表2 ON 表1.共同列 = 表2.共同列;

💬 用同一组数据再看 JOIN 写法

上面两张表的数据不变，只是把“连接条件”从 WHERE 里挪到了 ON 里，结果是一样的。

SQL 例子

SELECT student.name, class.class_name
FROM student
  INNER JOIN class ON student.class_id = class.class_id;

JOIN 写法在干什么

SELECT：还是取“姓名”和“班级名称”这两个字段。

FROM student INNER JOIN class
表示把 student 表和 class 表连接起来查。

ON student.class_id = class.class_id
表示连接条件是：两张表的 class_id 必须相等。

JOIN 写法怎么读

可以读成：

从 student 表中，把它和 class 表做内连接，连接条件是 student.class_id 等于 class.class_id，再取出 name 和 class_name 两个字段。

这里的 ON 可以直接理解成：“连接条件是……”

查询结果示意

name	class_name
张三	软件1班
李四	大数据1班
王五	软件1班

推荐记法

ON：写“怎么连接”

student.class_id = class.class_id

WHERE 做什么

WHERE：写“筛选谁”

class.class_name = '软件1班'

💡 为什么更推荐 JOIN 写法

因为它把两件事分开了：ON 管连接，WHERE 管筛选。 代码更清楚，出错也更少。

五种 JOIN 类型一览

INNER JOIN

只保留两表都匹配的行

LEFT JOIN

保留左表全部，右表无匹配填 NULL

RIGHT JOIN

保留右表全部，左表无匹配填 NULL

FULL JOIN

保留两表全部，无匹配都填 NULL

CROSS JOIN

所有行的笛卡尔积（全排列）

内连接查询（INNER JOIN）

📖 定义

内连接只返回两张表中满足连接条件的行。不满足条件的行会被"丢弃"，不会出现在结果中。

💬 人话翻译

就像"配对游戏"——只有两边都能配上对的人才能留下来，没配上对的就出局。

例 6-32：两表内连接

查询学号为"s5"的学生的选课信息，列出学号、姓名和课程号。

📋 先看原始表（本例用到的是这两张表）

学生表 s

sno	sn	maj
s1	李明	计算机
s2	王彤	软件工程
s5	赵敏	人工智能
s6	刘洋	计算机

选课表 sc

sno	cno	score
s1	c1	85
s2	c2	92
s5	c2	95
s5	c3	87

这道题的关键是：s 表中有学生姓名，sc 表中有课程号，它们都包含 sno，所以可以通过 sno 把两张表连起来。

SQL

SELECT s.sno, s.sn, sc.cno
FROM s, sc
WHERE s.sno = 's5' AND s.sno = sc.sno;

这句 SQL 在干什么？

SELECT s.sno, s.sn, sc.cno：取出学生表里的学号和姓名，以及选课表里的课程号。

FROM s, sc：这次查询要同时看学生表和选课表。

WHERE s.sno = 's5'：先锁定学号是 s5 的学生。

AND s.sno = sc.sno：再要求两张表里的学号相等，表示它们是同一个学生。

可以这样读

从 学生表 s 和 选课表 sc 里，

选择 学号、姓名、课程号，

条件是：学生学号等于 s5，

并且 s 表的学号等于 sc 表的学号。

SQL

SELECT s.sno, s.sn, sc.cno
FROM s INNER JOIN sc
  ON s.sno = sc.sno
WHERE s.sno = 's5';

💡 这里更容易讲清楚分工

ON 负责说明"两张表怎样连"，这里是按 sno 相等来连；WHERE 负责说明"最终只看谁"，这里是只看 s5。

▶ 查询结果

sno	sn	cno
s5	赵敏	c2
s5	赵敏	c3

⚠️ 注意：字段同名必须加表前缀

学生表 s 和选课表 sc 都有 sno 字段。如果直接写 sno，数据库不知道你指的是哪张表的 sno，会报"二义性"错误。
解决办法：写成 s.sno 或 sc.sno。

🧭 规范写法 vs. 能跑但不推荐的写法

下面这句在很多数据库里可能不报错，因为 sn 只出现在学生表里，cno 只出现在选课表里，数据库还能猜到你要的是谁：

能跑但不推荐

SELECT s.sno, sn, cno
FROM s, sc
WHERE s.sno = 's5' AND s.sno = sc.sno;

但教学时更推荐把字段写完整：

推荐规范写法

SELECT s.sno, s.sn, sc.cno
FROM s, sc
WHERE s.sno = 's5' AND s.sno = sc.sno;

原因很简单：更清楚、更统一、以后表一多也不容易出错。

随堂练 1

在这个例子里，真正把学生表和选课表"连起来"的条件是哪一句？

A. SELECT s.sno, sn, cno

B. FROM s, sc

C. s.sno = sc.sno

D. s.sno = 's5'

解析：真正的连接条件是 s.sno = sc.sno。它表示两张表里的学号要相同，才能说明它们描述的是同一个学生。

例 6-33：三表内连接

查询所有授课教师的教师号、姓名和课程名，按教师号升序排列。

这里涉及三张表：教师表 t → 授课表 tc → 课程表 c，通过链式连接把它们串起来。

📋 先看三张原始表

教师表 t

tno	tn
t1	李力
t2	王平
t3	陈琳
t4	赵晴
t5	刘波
t6	张刚

授课表 tc

tno	cno
t1	c2
t2	c3
t3	c1
t4	c2
t5	c4

课程表 c

cno	cn	ct
c1	程序设计基础	48
c2	数据库	64
c3	数学	56
c4	网页设计	32

这三张表的连接思路是：t.tno = tc.tno，再用 tc.cno = c.cno。也就是先找到"谁教课"，再找到"教的是哪门课"。

1

教师表 t
先拿到教师号和教师名

2

授课表 tc
用 tno 找教师教了哪门课

3

课程表 c
再用 cno 找课程名称

SQL

SELECT t.tno, t.tn, c.cn
FROM t, tc, c
WHERE t.tno = tc.tno AND tc.cno = c.cno
ORDER BY t.tno;

SQL

SELECT t.tno, t.tn, c.cn
FROM t
  INNER JOIN tc ON t.tno = tc.tno
  INNER JOIN c  ON tc.cno = c.cno
ORDER BY t.tno;

💡 可以这样读

先从教师表 t 出发，用 t.tno = tc.tno 找到每位教师对应的授课记录；再用 tc.cno = c.cno 找到课程名称；最后按教师号升序排列。

▶ 查询结果

tno	tn	cn
t1	李力	数据库
t2	王平	数学
t3	陈琳	程序设计基础
t4	赵晴	数据库
t5	刘波	网页设计

随堂练 2

在这个三表连接里，哪一列把授课表 tc 和课程表 c 连接起来？

A. t.tno

B. tc.cno = c.cno

C. tn = cn

D. ORDER BY tno

解析：tc 表里有课程号 cno，c 表里也有课程号 cno，所以这两张表就是通过 cno 连起来的。

例 6-34：内连接 + 分组 + HAVING

查询选课人数在 3 人及以上的课程号、课程名和选课人数。

📋 先看原始表（本例只用课程表 c 和选课表 sc）

课程表 c

cno	cn
c1	程序设计基础
c2	数据库
c3	数学
c4	网页设计

选课表 sc

sno	cno
s1	c1
s2	c1
s4	c1
s2	c2
s3	c2
s5	c2
s1	c3
s5	c3

从原始数据直接数一数也能看出来：c1 有 3 人选，c2 有 3 人选，c3 只有 2 人选，c4 没有人选。

SQL

SELECT c.cno, c.cn, COUNT(sc.sno) AS 选课人数
FROM c INNER JOIN sc
  ON c.cno = sc.cno
GROUP BY c.cno, c.cn
HAVING COUNT(sc.sno) >= 3;

执行逻辑拆解

① 先用 INNER JOIN 把课程表和选课表按 cno 连起来。

② 再用 GROUP BY 按课程号和课程名分组。

③ 然后用 COUNT 统计每门课被选了多少次。

④ 最后用 HAVING 只保留人数 ≥ 3 的课程。

可以这样读

从课程表和选课表中，

先按课程号把两张表连起来，

再按课程分组，统计每门课的选课人数，

最后只保留选课人数不少于 3 人的课程。

▶ 查询结果

cno	cn	选课人数
c1	程序设计基础	3
c2	数据库	3

随堂练 3

根据上面的原始数据，哪门课会被 HAVING COUNT(sc.sno) >= 3 过滤掉？

A. c1 程序设计基础

B. c2 数据库

C. c3 数学

D. c1 和 c2

解析：c3 只有 2 条选课记录，所以不会出现在结果里。c1 和 c2 都有 3 人选，能保留下来。

外连接查询（OUTER JOIN）

📖 定义

外连接在内连接的基础上，还会保留主表中不满足连接条件的行。不匹配的列用 NULL 填充。

💬 人话翻译

内连接是"配对成功才留下"，外连接是"就算没配上，主表的人照样留下，只是配对信息那一栏填空白"。
就像班级照相——即使某位同学当天请假没来，花名册上照样要有他的名字。

LEFT JOIN（左外连接）

以 左边的表 为主表。左表所有行都保留，右表没匹配到的填 NULL。

RIGHT JOIN（右外连接）

以 右边的表 为主表。右表所有行都保留，左表没匹配到的填 NULL。

例 6-35：LEFT JOIN 保留所有教师

查询所有教师的教师号、姓名和授课程名（没有授课的教师也要显示）。

📋 继续沿用三张原始表

教师表 t

tno	tn
t1	李力
t2	王平
t3	陈琳
t4	赵晴
t5	刘波
t6	张刚

授课表 tc

tno	cno
t1	c2
t2	c3
t3	c1
t4	c2
t5	c4

课程表 c

cno	cn
c1	程序设计基础
c2	数据库
c3	数学
c4	网页设计

注意观察：教师表里有 t6 张刚，但授课表里没有 t6。这个例子正好能说明为什么要用 LEFT JOIN。

SQL

SELECT t.tno, t.tn, c.cn
FROM t LEFT OUTER JOIN tc ON t.tno = tc.tno
     LEFT OUTER JOIN c ON tc.cno = c.cno
ORDER BY t.tno;

为什么这里要用 LEFT JOIN？

因为题目要求：没有授课的教师也要显示。

如果用 INNER JOIN，t6 张刚会被丢掉。

如果用 LEFT JOIN，左边教师表里的每个人都会保留。

右边没匹配到的课程信息，就显示为 NULL。

可以这样读

从教师表出发，

先把教师和授课表按教师号连起来，

再把授课表和课程表按课程号连起来，

即使没有授课记录，也保留该教师这一行。

▶ 运行结果（对比内连接）

与例 6-33 的内连接结果相比，本查询多出了张刚老师（t6），他没有授课记录，课程名显示为 NULL：

tno	tn	cn
t1	李力	数据库
t2	王平	数学
t3	陈琳	程序设计基础
t4	赵晴	数据库
t5	刘波	网页设计
t6	张刚	NULL

随堂练 4

根据上面的原始表，哪位教师会因为 LEFT JOIN 而被额外保留下来？

A. 李力（t1）

B. 王平（t2）

C. 张刚（t6）

D. 刘波（t5）

解析：教师表里有 t6，但授课表里没有 t6。内连接时他会消失，左连接时会保留，课程名显示为 NULL。

例 6-36：左连接与右连接的等价转换

查询所有学生的学号、姓名、课程号和成绩（没有选课的学生也要显示）。

📋 本例用到的原始表

学生表 s

sno	sn
s1	李明
s2	王彤
s3	陈晨
s4	周宇
s5	赵敏
s6	刘洋

选课表 sc

sno	cno	score
s1	c1	85
s2	c2	92
s3	c2	78
s5	c3	87

注意：学生表里有 s6 刘洋，但选课表里没有 s6。这个学生就是观察外连接效果的关键。

SQL

SELECT s.sno, s.sn, sc.cno, sc.score
FROM s LEFT OUTER JOIN sc
  ON s.sno = sc.sno;

学生表 s 在左边是主表 → 所有学生都保留，包括没有选课的 s6。

SQL

SELECT s.sno, s.sn, sc.cno, sc.score
FROM sc RIGHT OUTER JOIN s
  ON s.sno = sc.sno;

把学生表放到右边，再用 RIGHT JOIN，本质上还是在保留学生表全部记录。

▶ 查询结果示意

sno	sn	cno	score
s1	李明	c1	85
s2	王彤	c2	92
s3	陈晨	c2	78
s4	周宇	NULL	NULL
s5	赵敏	c3	87
s6	刘洋	NULL	NULL

💡 小贴士

LEFT JOIN 和 RIGHT JOIN 可以互相转换，只要交换两张表的位置即可。实际开发中 LEFT JOIN 用得更多，因为阅读起来更符合"主表在前"的直觉。

交叉连接查询（CROSS JOIN）

📖 定义

交叉连接将第一张表的每一行与第二张表的每一行配对，生成所有可能的组合。结果行数 = 表1行数 × 表2行数。

💬 人话翻译

假设你有 3 件上衣和 4 条裤子，交叉连接就是列出所有 3 × 4 = 12 种搭配方案——每件上衣都要和每条裤子搭配一次。

例 6-37：交叉连接

📋 为了便于观察，这里先截取两张小表来演示

教师表 t（截取前 2 行）

tno	tn
t1	李力
t2	王平

课程表 c（截取前 3 行）

cno	cn
c1	程序设计基础
c2	数据库
c3	数学

这里只有 2 位教师和 3 门课程，所以交叉连接的结果行数是 2 × 3 = 6 行。

SQL — 例 6-37

SELECT *
FROM t CROSS JOIN c;

▶ 结果示意（6 行）

tno	tn	cno	cn
t1	李力	c1	程序设计基础
t1	李力	c2	数据库
t1	李力	c3	数学
t2	王平	c1	程序设计基础
t2	王平	c2	数据库
t2	王平	c3	数学

随堂练 5

如果教师表有 5 行，课程表有 4 行，那么 CROSS JOIN 结果有多少行？

A. 9 行

B. 16 行

C. 20 行

D. 40 行

解析：交叉连接是全排列，结果行数 = 表1行数 × 表2行数，所以是 5 × 4 = 20。

⚠️ 注意：结果集可能非常大

交叉连接没有 ON 条件，是纯粹的"全排列"。如果两张表各有 1000 行，结果就是 100 万行！实际开发中很少直接使用，通常只用于生成测试数据或特定的组合场景。

自连接查询（Self Join）

📖 定义

自连接是一张表与自己进行连接查询。通过给同一张表取两个不同的别名，就像复制了一份一样来进行比较。

💬 人话翻译

就像在班级里找"比小明高的同学"——你需要把同一份花名册复制两份，一份锁定"小明"的身高，另一份去逐个比较。

例 6-38：同一表内的"大于"比较

查询比"程序设计基础"课时高的课程号、课程名和课时。

📋 原始表：课程表 c

课程表 c

cno	cn	ct
c1	程序设计基础	48
c2	数据库	64
c3	数学	56
c4	网页设计	32

先找到"程序设计基础"的课时是 48，再去找所有课时比 48 大的课程。

SQL

-- 给课程表取两个别名：x 用来找结果，y 用来锁定"程序设计基础"
SELECT x.cno AS 课程号, x.cn AS 课程名, x.ct AS 课时
FROM c AS x INNER JOIN c AS y
  ON x.ct > y.ct AND y.cn = '程序设计基础';

💬 执行逻辑

y 锁定"程序设计基础"那一行 → 拿到它的课时值 48 → x 遍历课程表，只要课时比 48 大，就保留。
本质上 x 和 y 是同一张表 c 的两个"分身"。

▶ 查询结果

课程号	课程名	课时
c2	数据库	64
c3	数学	56

例 6-39：同一表内的"等值"比较

查询与学生"王彤"专业相同的学号和姓名。

📋 原始表：学生表 s

学生表 s

sno	sn	maj
s1	李明	计算机
s2	王彤	软件工程
s3	陈晨	数据科学
s4	周宇	软件工程
s5	赵敏	人工智能

先在学生表里找到"王彤"的专业是 软件工程，再找出所有专业同样是软件工程的学生。

SQL

SELECT x.sno, x.sn
FROM s AS x INNER JOIN s AS y
  ON x.maj = y.maj AND y.sn = '王彤';

▶ 查询结果

sno	sn
s2	王彤
s4	周宇

❌ 易错点

自连接结果中通常会包含"王彤"自己（因为她的专业和自己的专业当然相同）。如果题目要求排除本人，需要额外加条件 AND x.sn != '王彤'。

随堂练 6

如果题目要求"查询与王彤专业相同、但不包含王彤本人"，应该补哪一条条件？

A. AND y.sn != '王彤'

B. AND x.sn != '王彤'

C. AND x.maj != y.maj

D. AND x.sno = y.sno

解析：y 的任务是锁定王彤，所以不能把 y 排除掉。真正要从结果中去掉的是 x 这一侧的王彤，因此加 AND x.sn != '王彤'。

本节小结

📋 核心要点回顾

1. 多表查询有两种写法：WHERE 连接和 JOIN 连接，推荐使用 JOIN 写法，语义更清晰。

2. INNER JOIN（内连接）只保留两表都匹配的行——"配不上就出局"。

3. LEFT/RIGHT JOIN（外连接）保留主表所有行，没匹配到的填 NULL——"即使没配上也要留名"。

4. CROSS JOIN（交叉连接）是全排列，结果行数 = 表1 × 表2，慎用。

5. 自连接是一张表和自己连接，用别名区分"两个分身"，常用于同表内的比较。

6. 多表中存在同名字段时，必须加表名前缀（如 s.sno），否则会报二义性错误。

课末快练（沿用本节原始数据）

📋 做题前先看参考数据

学生表 s

sno	sn
s1	李明
s2	王彤
s6	刘洋

选课表 sc

sno	cno
s1	c1
s2	c2

教师表 t（局部）

tno	tn
t1	李力
t6	张刚

这 3 道题都基于上面这组小数据，尽量让学生一边看表、一边选答案。

练习 1 · 模仿题

如果要查询所有学生的学号、姓名及其选课课程号，并且要求没有选课的学生也显示，应该用哪种连接？

A. INNER JOIN

B. LEFT JOIN（以学生表为主表）

C. CROSS JOIN

D. 自连接

解析：因为题目要求没有选课的学生也显示，例如上面的 s6 刘洋，所以必须保留学生表全部记录，应使用 LEFT JOIN。

练习 2 · 理解题

如果执行 SELECT * FROM s CROSS JOIN sc;，上面这组小数据会得到多少行？(学生表 s 有 3 行，选课表 sc 有 2 行)

A. 2 行

B. 6 行

C. 5 行

D. 1 行

解析：学生表 s 有 3 行，选课表 sc 有 2 行，交叉连接结果是 3 × 2 = 6 行。

练习 3 · 迁移题

如果要查询所有教师及其课程名，并且没有授课的教师也显示，例如 t6 张刚，那么哪种说法正确？

A. 应以教师表 t 为主表使用 LEFT JOIN

B. 一定要用 CROSS JOIN

C. INNER JOIN 也能自动保留张刚

D. 只能用自连接

解析：要保留没有授课记录的教师，就要把教师表放在主表位置，也就是使用 LEFT JOIN（或等价的 RIGHT JOIN 变形）。